#encoding: utf-8
=begin
Usage:      用于更新商品代码
=end

require 'os'
require 'simple-spreadsheet' #用于读取xlsx文件，spreadsheet gem读xlsx会报签名错误

load 'rds_api.rb'

#先处理玉琴给的第一批数据，位置填到第一列了，所以会判断一下，如果是
def updateBarcode1 xlsx

        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            unik = s.cell(line,2)
            barcode = s.cell(line,5)
            pid = s.cell(line,1)

            if pid.class == String && pid.size > 4
              barcode = pid #这是被玉琴重写过的,实际是
              barcode = barcode.gsub("\n","") #里面有一些自带换行符
              ucounter += 1
              sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
              sqls += [sql]
            end

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB1
  sqls = updateBarcode1 ( OS.windows? ? ".\\data\\bcp-barcode-1.xlsx" : "./data/bcp-barcode-1.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB1.sql" : "./data/updateB1.sql"
  File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  commitTrans sqls
end
#updateB1

=begin
  2021年12月31日盘过库之后，根据导出的盘点表，将所有非管家代码的商品，更新为94开头的十位自编代码
  配合数据库操作，直接导出产品
=end
def updateBarcode2 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            unik = s.cell(line,22)
            barcode = s.cell(line,3)
            pid = s.cell(line,1)

            if barcode.size < 10
              ucounter += 1
              barcode = ( 9400000000 + ucounter ).to_s
              sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
              sqls += [sql]
            end
        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB2
  sqls = updateBarcode2 ( OS.windows? ? ".\\data\\stocks-M1-201231.xls" : "./data/stocks-M1-201231.xls" )
  logfn = OS.windows? ? ".\\data\\updateB2.sql" : "./data/updateB2.sql"
  File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  commitTrans sqls
end
#updateB2

=begin
  给直发商品导入编码
=end
def updateBarcode3 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            unik = s.cell(line,22)
            barcode = s.cell(line,3)
            pid = s.cell(line,1)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
            sqls += [sql]

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB3
  sqls = updateBarcode3 ( OS.windows? ? ".\\data\\bcp-barcode-3.xlsx" : "./data/bcp-barcode-3.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB3.sql" : "./data/updateB3.sql"
  puts sqls
  #File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  #commitTrans sqls
end
#updateB3

=begin
  修正部分商品编码
=end
def updateBarcode4 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            old_barcode = s.cell(line,1)
            barcode = s.cell(line,2)
            pid = s.cell(line,1)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where bar_code = '#{old_barcode}' and type = 0 and `unique` not like '%-%';"
            sqls += [sql]
        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB4
  sqls = updateBarcode4 ( OS.windows? ? ".\\data\\bcp-barcode-4.xlsx" : "./data/bcp-barcode-4.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB4.sql" : "./data/updateB4.sql"
  puts sqls
  #File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  #commitTrans sqls
end
#updateB4

=begin
  给直发商品2导入编码
=end
def updateBarcode5 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            unik = s.cell(line,22)
            barcode = s.cell(line,3)
            pid = s.cell(line,1)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
            sqls += [sql]

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB5
  sqls = updateBarcode5 ( OS.windows? ? ".\\data\\bcp-barcode-5.xls" : "./data/bcp-barcode-5.xls" )
  logfn = OS.windows? ? ".\\data\\updateB5.sql" : "./data/updateB5.sql"
  File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  commitTrans sqls
end
#updateB5

=begin
  清除所有pospal code
=end
def updateBarcode6 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            unik = s.cell(line,1)
            barcode = s.cell(line,6)
            pid = s.cell(line,1)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '' where `unique` = '#{unik}';"
            sqls += [sql]

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB6
  sqls = updateBarcode6 ( OS.windows? ? ".\\data\\clean-barcode-6.xlsx" : "./data/clean-barcode-6.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB6.sql" : "./data/updateB6.sql"
  #File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  #commitTrans sqls
end
#updateB6

=begin
  更新手工修正过重复barcode和非法barcode的表
=end
def updateBarcode7 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            barcode = s.cell(line,1)
            unik = s.cell(line,4)
            pid = s.cell(line,1)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
            sqls += [sql]

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB7
  sqls = updateBarcode7 ( OS.windows? ? ".\\data\\fix-barcode-7.xlsx" : "./data/fix-barcode-7.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB7.sql" : "./data/updateB7.sql"
  puts sqls
  #File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  #commitTrans sqls
end
#updateB7

=begin
  更新手工修正过因00导致错误的编码
=end
def updateBarcode8 xlsx
        sqls = []
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        ucounter = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1

            barcode = s.cell(line,1)
            unik = s.cell(line,4)

            ucounter += 1
            sql = "update  crmeb.eb_store_product_attr_value set bar_code = '#{barcode}' where `unique` = '#{unik}';"
            sqls += [sql]

        end
        puts "\ndone. #{line_idx} #{ucounter}"
        return sqls
end

def updateB8
  sqls = updateBarcode8 ( OS.windows? ? ".\\data\\fix-barcode-8.xlsx" : "./data/fix-barcode-8.xlsx" )
  logfn = OS.windows? ? ".\\data\\updateB8.sql" : "./data/updateB8.sql"
  puts sqls
  #File.open(logfn,"a:UTF-8") { |f| f.write sqls.join("\n")+"\n" }
  #commitTrans sqls
end
#updateB8

=begin
  更新所有营销商品的barcode
=end
def update_mp_barcode mp_table_name
  sqls = []
  sql = "select unik, bar_code from crmeb.#{mp_table_name}"
  queryRds(sql).each do |res|
    sqls += ["SELECT pv.product_id, pv.suk, pv.type, pv.bar_code, pv.`unique`
      FROM crmeb.eb_store_product_attr_value pv, crmeb.#{mp_table_name} AS sec 
      WHERE sec.primary_pid = pv.product_id AND pv.type = 0 AND sec.suk = pv.suk and pv.suk!= ''
      AND sec.`unik` = '#{res['unik']}'; ##{res['unik']}"]
  end
  usqls = []
  sqls.each do |sql|
    queryRds(sql).each do |res|
      unik = sql.split('#')[1]
      new_barcode = res['bar_code']
      usqls += ["update crmeb.eb_store_product_attr_value set bar_code = '#{new_barcode}' where `unique` = '#{unik}'"] if !new_barcode.empty?
    end
  end
  ap usqls #commitTrans usqls
end
update_mp_barcode 'seckill_pv'
update_mp_barcode 'bargain_pv'
update_mp_barcode 'combination_pv'
